package boogui.movie;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import boogui.db.BooguiDAO;
import boogui.db.IBooguiDAO;
import boogui.member.UserVO;

public class MovieReserveDAO implements IMovieSeatDAO {

	private String insertSeatSQL = "insert into seatinfo values()";
	//private String selectSeatSQL = "select * from seatinfo where showno = ? order by showno desc";
	private String selectSeatSQL = "select seat.* from seatinfo seat"
			+ " inner join showing s on seat.showno = s.showno"
			+ " inner join movies m on s.movno = m.movno"
			+ " inner join cinema c on c.cineno = s.cineno"
			+ " where m.title = ? and location = ? and cinename = ?"
			+ " and theater = ? and sdate = to_date(?,'YYYYMMDD') and stime = ?";
	private String insertReserveSQL = "insert into reservation values (movie.nextval,"
			+ "(select memno from member where memid = ?),"
			+ "(select movno from movies where title = ?),"
			+ " (select cineno from cinema where cinename = ? and theater = ?),"
			+ " ?, ?, ?, ?,?,?,?,?,?,?)";
/*	private String insertReserveSQL = "insert into reservation values (movie.nextval,"
			+ "(select memno from member where memid = ?),"
			+ "(select movno from movies where title = ?),"
			+ " (select cineno from cinema where cinename = ? and theater = ?),"
			+ " ?, ?, ?, ?)";	// 131209
*/	
	private String selectRsvSQL = "select * from reservation where memid = ?";
	
	/*private String selectRsvTitleSQL = "select title from movies where movno in " 
			+ "(select movno from reservation r inner join member m on r.memno"
			+ "= m.memno where memid = ?)";
	
	private String selectRsvCinemaSQL = "select cinename, theater from cinema"
			+ " where cineno in (select cineno from reservation r"
			+ " inner join member m on r.memno = m.memno where memid = ?)";
	
	private String selectRsvDateSQL = "select sdate, stime from showing"
			+ " where showno in (select showno from reservation r"
			+ " inner join member m on r.memno = m.memno where memid = ?)"; 	
	*/
	
	private IBooguiDAO bd = null;

	public MovieReserveDAO() {
		bd = new BooguiDAO();
	}

	@Override
	public boolean insertSeat(S_MovieSeatDialog m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;

		try {
			conn = bd.openDatabase();
			pstmt = conn.prepareStatement(insertSeatSQL);
			pstmt.executeUpdate();
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			bd.closeDatabase(conn, pstmt, null);
		}
		return result;
	}

	@Override
	public List<String> selectSeat(MovieVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<String> result = new ArrayList<>();

		try {
			conn = bd.openDatabase();
			pstmt = conn.prepareStatement(selectSeatSQL);			
			pstmt.setString(1, m.getMovtitle());
			pstmt.setString(2, m.getLocation());
			pstmt.setString(3, m.getCinename());
			pstmt.setString(4, m.getTheater());
			pstmt.setString(5, m.getSdate());
			pstmt.setString(6, m.getStime());
			/*pstmt.setString(1, "564");*/
			rs = pstmt.executeQuery();
			while (rs.next()) {				
				for (int i = 2; i <= 181; i++) {
					result.add(rs.getString(i));
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			bd.closeDatabase(conn, pstmt, rs);
		}
		return result;
	}
	
	public String selectShowno(MovieVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			conn = bd.openDatabase();
			pstmt = conn.prepareStatement(selectSeatSQL);			
			pstmt.setString(1, m.getMovtitle());
			pstmt.setString(2, m.getLocation());
			pstmt.setString(3, m.getCinename());
			pstmt.setString(4, m.getTheater());
			pstmt.setString(5, m.getSdate());
			pstmt.setString(6, m.getStime());
			/*pstmt.setString(1, "564");*/
			rs = pstmt.executeQuery();
			while (rs.next()) {	
				result = rs.getString("showno");				
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			bd.closeDatabase(conn, pstmt, rs);
		}
		return result;
	}
	
	@Override
	public boolean updateSeat(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;		
		try {
			conn = bd.openDatabase();
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			bd.closeDatabase(conn, pstmt, null);
		}
		return result;
	}
	
	public boolean insertReservation(MovieVO m, UserVO u) {
		Connection conn = null;
		PreparedStatement pstmt = null;		
		boolean result = false;
		
		try {
			conn = bd.openDatabase();
			pstmt = conn.prepareStatement(insertReserveSQL);			
			pstmt.setString(1, u.getUserid());
			pstmt.setString(2, m.getMovtitle());
			pstmt.setString(3, m.getCinename());
			pstmt.setString(4, m.getTheater());
			pstmt.setString(5, m.getPeoplenum());
			pstmt.setString(6, m.getSeat());
			pstmt.setString(7, m.getPrice());
			pstmt.setString(8, this.selectShowno(m));
			pstmt.setString(9, u.getUserid());
			pstmt.setString(10, m.getMovtitle());
			pstmt.setString(11, m.getSdate());
			pstmt.setString(12, m.getStime());
			pstmt.setString(13, m.getCinename());
			pstmt.setString(14, m.getTheater());
			
			pstmt.executeUpdate();
			
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			bd.closeDatabase(conn, pstmt, null);
		}
		return result;
	}
	
	public List<MovieVO> selectReservation(UserVO u) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<MovieVO> result = new ArrayList<>();
		
		try {
			
			conn = bd.openDatabase();
			pstmt = conn.prepareStatement(selectRsvSQL);			
			pstmt.setString(1, u.getUserid());
			rs = pstmt.executeQuery();			
			
			while (rs.next()) {
				MovieVO m = new MovieVO();
				m.setMovtitle(rs.getString("title"));
				m.setSdate(rs.getString("sdate"));
				m.setStime(rs.getString("stime"));
				m.setCinename(rs.getString("cinename"));
				m.setTheater(rs.getString("theater"));
				m.setSeat(rs.getString("seat"));
				result.add(m);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			bd.closeDatabase(conn, pstmt, rs);
		}
		return result;
	}
	

}